﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;


namespace Util
{
    public class DBConnection
    {
        static String ServerName;
        static String DBName;
        static String UserName;
        static String Password;
        SqlConnection con;

        static DBConnection()
        {
            ServerName = "HUYNVK-PC";
            DBName = "BlogsProvider";
            UserName = "BlogsProvider";
            Password = "123456";
        }

        public DBConnection()
        {
            con = GetConnection();
        }

        /// <summary>
        /// Close connection to database.
        /// </summary>
        public void Close()
        {
            try
            {
                con.Dispose();
            }
            catch (Exception ex)
            {
                Log.LogError(ex.StackTrace);
            }
        }

        /// <summary>
        /// Get Database Connection.
        /// </summary>
        /// <returns>Connection to Database.</returns>
        public static SqlConnection GetConnection()
        {
            String connectionString = "Data Source=" + ServerName +
                                    ";Initial Catalog=" + DBName +
                                    ";User Id=" + UserName +
                                    ";Password=" + Password + ";";
            //String connectionString = ConfigurationManager.ConnectionStrings["BlogsProviderConnectionString"].ToString();
            SqlConnection connection = new SqlConnection(connectionString);
            return connection;
        }

        /// <summary>
        /// Execute Query and return SqlDataReader value.
        /// </summary>
        /// <param name="QueryString">Query string to be executed.</param>
        /// <returns>SqlDataReader value.</returns>
        public SqlDataReader ExecuteReader(String QueryString)
        {
            SqlCommand Comd = new SqlCommand(QueryString, con);
            SqlDataReader reader = null;
            try
            {
                con.Open();
                reader = Comd.ExecuteReader();
            }
            catch(Exception ex)
            {
                Log.LogError(ex.StackTrace);
            }
            finally
            {
                try
                {
                    Comd.Dispose();
                }
                catch (Exception ex)
                {
                    Log.LogError(ex.StackTrace);
                }
            }

            return reader;
        }

        /// <summary>
        /// Execute Query and return SqlDataReader value.
        /// </summary>
        /// <param name="QueryString">Query string to be executed.</param>
        /// <param name="Comd">SqlCommand to execute.</param>
        /// <returns>SqlDataReader value.</returns>
        public SqlDataReader ExecuteReader(SqlCommand Comd, String QueryString)
        {
            SqlDataReader reader = null;
            try
            {
                con.Open();
                Comd.Connection = con;
                Comd.CommandText = QueryString;
                reader = Comd.ExecuteReader();
            }
            catch (Exception ex)
            {
                Log.LogError(ex.StackTrace);
            }
            finally
            {
                try
                {
                    Comd.Dispose();
                }
                catch (Exception ex)
                {
                    Log.LogError(ex.StackTrace);
                }
            }

            return reader;
        }

        /// <summary>
        /// Execute Query and return SqlDataReader value.
        /// </summary>
        /// <param name="QueryString">Query string to be executed.</param>
        /// <returns>Amount of rows return after executed Query string.</returns>
        public int ExecuteNonQuery(String QueryString)
        {
            SqlCommand Comd = new SqlCommand(QueryString, con);
            int result = 0;
            try
            {
                con.Open();
                result = Comd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Log.LogError(ex.StackTrace);
            }
            finally
            {
                try
                {
                    Comd.Dispose();
                }
                catch (Exception ex)
                {
                    Log.LogError(ex.StackTrace);
                }
            }

            return result;
        }

        /// <summary>
        /// Execute Query and return SqlDataReader value.
        /// </summary>
        /// <param name="QueryString">Query string to be executed.</param>
        /// <param name="Comd">SqlCommand to execute.</param>
        /// <returns>Amount of rows return after executed Query string.</returns>
        public int ExecuteNonQuery(SqlCommand Comd, String QueryString)
        {
            int result = 0;
            try
            {
                con.Open();
                Comd.Connection = con;
                Comd.CommandText = QueryString;
                result = Comd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Log.LogError(ex.StackTrace);
            }
            finally
            {
                try
                {
                    Comd.Dispose();
                }
                catch (Exception ex)
                {
                    Log.LogError(ex.StackTrace);
                }
            }

            return result;
        }
    }
}